# -*- coding: utf-8 -*-
import os
import re
import xlrd
import xlsxwriter


def get_table_name():
    items = os.listdir(".")
    tablelist = []
    for names in items:
        match = re.match('(.*?)(.xls)$', names)
        if match:
            tablelist.append(match.group())

        search = re.search('(.*?)(.xlsx)$', names)
        if search:
            tablelist.append(search.group())

    # tablelist.sort(key=lambda x: int(x.split(".")[0]))
    tablelist.sort()

    return tablelist


def get_common_header(filename):
    common_header = []
    data = xlrd.open_workbook(filename)
    names = data.sheet_names()
    t_data = {}

    for name in names:
        t_common_header = []
        table = data.sheet_by_name(name)
        data.sheet_loaded(name)

        """取公共表头"""
        for i in range(4):
            t_common_header.append(table.row_values(i))

        t_data["common_header"] = t_common_header

    common_header.append(t_data)

    filelist = []
    filename = ""
    for a in common_header[0].values():
        filelist = a[:]

        filename = filelist[1][0]

    del filelist[1]
    # print(filename)
    common_header[0]["common_header"] = filelist
    # print(common_header)
    if not os.path.exists(os.getcwd() + "_result/"):
        os.makedirs(os.getcwd() + "_result/")

    return filename, common_header


def get_data(filename):
    data = xlrd.open_workbook(filename)
    names = data.sheet_names()

    for name in names:
        table = data.sheet_by_name(name)
        data.sheet_loaded(name)
        max_rows = table.nrows

        sheet_name = []
        sheet_head = []
        sheet_data = []
        temp_data = []
        """取数据"""
        j = 0
        k = 0
        l = 0
        for i in range(max_rows):
            j += 1
            if j > 4:
                if '\u4e00' <= table.row_values(i)[0] <= '\u9fa5':
                    k += 1
                    sheet_name.append("第" + table.row_values(i)[0] + "类")

                    temp_head = []
                    temp_head.append(table.row_values(i))
                    sheet_head.append(temp_head)
                    l = 0

                    if len(temp_data) != 0:
                        sheet_data.append(temp_data)
                        temp_data = []
                elif "（" in table.row_values(i)[0]:
                    l += 1

                    temp_head = table.row_values(i)[:]
                    temp_head[0] = str(k) + "-" + str(l)
                    sheet_group = []

                    if l == 1:
                        del sheet_name[-1]

                        del sheet_head[-1]
                        sheet_group.append(table.row_values(i - l))
                        sheet_group.append(temp_head)
                        sheet_head.append(sheet_group)
                    else:
                        sheet_group.append(temp_head)
                        sheet_head.append(sheet_group)

                    sheet_name.append("第" + str(k) + "-" + str(l) + "类")

                    if len(temp_data) != 0:
                        sheet_data.append(temp_data)
                        temp_data = []
                else:
                    temp_data.append(table.row_values(i))

        sheet_data.append(temp_data)
        # print(sheet_name)
        # print(sheet_head)
        # print(sheet_data)

        temp = []
        num = 0
        for i in sheet_head:
            i.extend(sheet_data[num])
            temp.append(i)
            num += 1

        return sheet_name, temp


def set_common_header(sheetName, common_header):
    # 设置行高
    sheetName.set_row(0, 45)
    sheetName.set_row(1, 25)
    sheetName.set_row(2, 25)
    # 设置列宽
    sheetName.set_column("A:A", 5.75)
    sheetName.set_column("B:B", 38.75)
    sheetName.set_column("C:E", 5.25)
    sheetName.set_column("F:F", 6.13)
    sheetName.set_column("G:G", 7.75)

    # 标题
    head_format = f.add_format({
        'font_name': '宋体',
        'font_size': 22,
        'bold': True,
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
    })
    # 第二行
    second_format = f.add_format({
        'font_name': '宋体',
        'font_size': 14,
        'border': 1,
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
    })
    # 第二行小字
    sm_format = f.add_format({
        'font_name': '宋体',
        'font_size': 11,
        'border': 1,
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
    })

    r = 0
    for i in common_header:
        for j in i.values():
            for k in j:
                c = 0
                for l in k:
                    if l == "人事档案目录":
                        sheetName.merge_range("A1:G1", "干部档案目录", head_format)
                    elif l == "序号":
                        sheetName.merge_range("A2:A3", "类\n号", second_format)
                    elif l == "材料名称":
                        sheetName.merge_range("B2:B3", l, second_format)
                    elif l == "材料形成时间":
                        sheetName.merge_range("C2:E2", l, sm_format)
                    elif l == "页数":
                        sheetName.merge_range("F2:F3", "页\n数", second_format)
                    elif l == "备注":
                        sheetName.merge_range("G2:G3", l, second_format)
                    elif l in "年月日":
                        sheetName.write(r, c, l, sm_format)
                    c += 1
                r += 1


def set_sheet_data(i, sheetName, sheet_head):
    # 小标题序号
    titlerank_format = f.add_format({
        'font_name': '宋体',
        'font_size': 12,
        'bold': True,
        'border': 1,
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
    })
    # 小标题
    title_format = f.add_format({
        'font_name': '宋体',
        'font_size': 12,
        'bold': True,
        'border': 1,
        'align': 'left',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
    })
    #
    data_format = f.add_format({
        'font_name': '宋体',
        'font_size': 12,
        'border': 1,
        'align': 'center',  # 水平居中
        'valign': 'vcenter',  # 垂直居中
        'text_wrap': True
    })

    r = 3
    for j in sheet_head:
        c = 0
        for k in j:
            sheetName.set_row(r, 30)
            if r == 3:
                sheetName.write(r, c, k, titlerank_format)
            elif r == 4 and "-" in i:
                sheetName.write(r, c, k, titlerank_format)
            elif c == 5:
                sheetName.write(r, c, k, titlerank_format)
            else:
                sheetName.write(r, c, k, data_format)
            c += 1
        r += 1


if __name__ == "__main__":
    for i in get_table_name():
        f = xlsxwriter.Workbook(os.getcwd() + "_result/" + get_common_header(i)[0] + ".xlsx")
        SH_NUM = 0  # 控制每个sheet表的小标题
        for j in get_data(i)[0]:
            sheetName = f.add_worksheet(j)

            set_common_header(sheetName, get_common_header(i)[1])
            set_sheet_data(j, sheetName, get_data(i)[1][SH_NUM])
            SH_NUM += 1

        f.close()

    print("结束")
    os.system("pause")
